package com.itheima.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBC_opengauss {


    //连接openGauss数据库
    public static Connection getConnect(String sourceURL, String username, String passwd) {
        //驱动类
        String driver = "org.postgresql.Driver";
        //数据库连接描述符
        Connection conn = null;
        try {
            //加载驱动
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        try {
            //创建连接
            conn = DriverManager.getConnection(sourceURL,username,passwd);
            System.out.println("Connection succeed!");
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return conn;
    }

    //调用isValid方法刷新缓存示例
    public static void isValid(Connection conn) {
        // conn通过调用isValid刷新缓存
        try {
            if (!conn.isValid(60)) {
                System.out.println("isValid Succeed for connection");
            }
            System.out.println("isValid Succeed for connection");
        } catch (SQLException e) {
            System.out.println("isValid Failed for connection");
            e.printStackTrace();
        }
    }

    //执行密态等值查询相关的创建密钥语句
    public static void createMasterKeyAndTable(Connection conn) {
        try {
            // 创建客户端主密钥
            Statement stmt = conn.createStatement();
            int rc = stmt.executeUpdate("CREATE CLIENT MASTER KEY ImgCMK1 WITH (KEY_STORE = localkms, KEY_PATH = \"key_path_value1\", ALGORITHM = RSA_2048);");
            // 创建列加密密钥
            int rc2 = stmt.executeUpdate("CREATE CLIENT MASTER KEY ImgCMK1 WITH ( KEY_STORE = localkms, KEY_PATH = \"key_path_value\" , ALGORITHM = RSA_2048);");
            int rc3 = stmt.executeUpdate("CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM  = AEAD_AES_256_CBC_HMAC_SHA256);");
            // 插入数据
            int rc4 = stmt.executeUpdate("CREATE TABLE creditcard_info (id_number    int, name  varchar(50) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC),credit_card  varchar(19) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC));");
            int rc5 = stmt.executeUpdate("INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393');");
            // 查询加密表
            ResultSet rs = stmt.executeQuery("select * from creditcard_info where name = 'joe';");
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 执行加密表的预编译SQL语句
    public static void executePreparedStatement(Connection conn) {
        try {
            //执行加密表的预编译SQL语句
            // 调用Connection的prepareStatement方法创建预编译语句对象
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO creditcard_info VALUES (1, ?, ?);");
            // 调用PreparedStatement的setShort设置参数
            pstmt.setInt(1, 2);
            pstmt.setString(2, "joy");
            pstmt.setString(3, "6219985678349800033");
            // 调用PreparedStatement的executeUpdate方法执行预编译SQL语句
            int rowcount = pstmt.executeUpdate();
            // 调用PreparedStatement的close方法关闭预编译语句对象
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 执行加密表的批处理操作
    public static void executeBatchProcessing(Connection conn) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO batch_table (id, name, address) VALUES (?,?,?)");
            int loopCount = 20;
            for (int i = 1; i < loopCount + 1; ++i) {
                pstmt.setInt(1, i);
                pstmt.setString(2, "Name " + i);
                pstmt.setString(3, "Address " + i);
                pstmt.addBatch();
            }
            int[] rowcount = pstmt.executeBatch();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public static void main(String[] args) {
        //输入自己openGauss数据库下的user和passwd及服务器URL
        String username = "joe" ;
        String passwd = "Bigdata@123";
        String sourceURL = "jdbc:postgresql://121.36.230.230:26000/db_tpcc?enable_ce=1";
        Connection conn = JDBC_opengauss.getConnect(sourceURL,username, passwd);
        try {
            // 调用刷新缓存方法
            if (conn != null) {
                isValid(conn);
            }
            // 执行创建密钥语句
            createMasterKeyAndTable(conn);
            // 执行预编译SQL语句
            executePreparedStatement(conn);
            // 执行批处理操作
            executeBatchProcessing(conn);
            // 关闭连接
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
